<?php
// +----------------------------------------------------------------------
// | 中联致远服务端框架
// +----------------------------------------------------------------------
// | Copyright (c) 2018 http://www.zhongliansoft.com All rights reserved.
// +----------------------------------------------------------------------
// | Author: tony <tanyu@zhongliansoft.com>
// +----------------------------------------------------------------------

namespace app\Manage\controller;
use app\common\model\Common as UModel;
use app\common\controller\Manage;
use app\common\model\BookReturn as BookReturnsModel;
use app\common\model\StudentInfo as StudentInfoModel;
use app\common\model\ViewGetDepts as ViewGetDeptsModel;
use app\common\model\ViewGetSpecicalties as ViewGetSpecicaltiesModel;
use app\common\model\ViewGetClasses as ViewGetClassesModel;
use app\common\model\ViewGetYears as ViewGetYearsModel;
use app\common\model\ViewGetSeasons as ViewGetSeasonsModel;
use app\common\model\ViewGetDirs as ViewGetDirsModel;
use app\common\model\ViewGetBookReward as ViewGetBookRewardModel;
use app\common\model\Config as ConfigsModel;
use think\facade\Request;
use think\Db;
use think\facade\Log;


class BookStatistics extends Manage
{

    /**
     * 教材发放情况统计
     * @return mixed
     * @throws \think\exception\DbException
     */
    public function index()
    {
        if(Request::isAjax())
        {
            // 参数解析
            $nd = input('year', 0);
            $jd = input('season', 0);
            $bm = input('dept', '');
            $zy = input('specialty', '');
            $bj = input('class', '');
            $dir = input('dir', '');
            $limit = input('limit', 0); 
            $page = input('page', 0);
            $index = ($page - 1) * $limit;
            
            // 这里直接调用SQL查询数据
            $model = new UModel();
            $list = $model->query("call proc_book_statistics(".$nd.",".$jd.",'".$bm."','".$zy."','".$bj."','".$dir."',".$index.",".$limit.");");
    
            if(count($list) == 0){
                $re['code'] = 0;
                $re['msg'] = '';
                $re['count'] = 0;
                $re['data'] = [];
                return $re;
            }

            $re['code'] = 0;
            $re['msg'] = '';
            $re['count'] = count($list[1]);
            $re['data'] = $list[0];
            return $re;
        }

        //获取部门列表
        $viewGetDeptsModel = new ViewGetDeptsModel();
        $dep_nameList = $viewGetDeptsModel->select();
        $this->assign('depList', $dep_nameList);

        //获取年度列表
        $viewGetYearsModel = new ViewGetYearsModel();
        $year_List = $viewGetYearsModel->select();
        $this->assign('yearList', $year_List);

        //获取季度列表
        $viewGetSeasonsModel = new ViewGetSeasonsModel();
        $season_List = $viewGetSeasonsModel->select();
        $this->assign('seasonList', $season_List);
        
        //获取方向列表
        $ViewGetDirsModel = new ViewGetDirsModel();
        $dir_List = $ViewGetDirsModel->select();
        $this->assign('dirList', $dir_List);

        //获取当前年度季度
        $ConfigsModel = new ConfigsModel();
        $year = $ConfigsModel->getYear();
        $season = $ConfigsModel->getSeason();
        $this->assign('curr_year', $year);
        $this->assign('curr_season', $season);


        return $this->fetch();
    }

    /**
     *  学生退费明细报表
     * @return array|mixed
     */
    public function detail()
    {
        $plan_year = input('plan_year', 0);
        $plan_season = input('plan_season', 0);
        $plan_code = input('plan_code', 0);
        $xh2 = input('xh', "");
        $xm2 = input('xm', "");

        if(Request::isAjax())
        {
            $limit = input('limit', 0); 
            $page = input('page', 0);
            $index = ($page - 1) * $limit;
            
            // 这里直接调用SQL查询数据
            $model = new UModel();
            $list = $model->query("call proc_book_statistics_detail(".$plan_year.",'".$plan_season."','".$plan_code."','".$xh2."','".$xm2."',".$index.",".$limit.");");
            
            if(count($list) == 0){
                $re['code'] = 0;
                $re['msg'] = '';
                $re['count'] = 0;
                $re['data'] = [];
                return $re;
            }

            $re['code'] = 0;
            $re['msg'] = '';
            $re['count'] = count($list[1]);
            $re['data'] = $list[0];
            return $re;
        }
        $this->assign('plan_year', $plan_year);
        $this->assign('plan_season', $plan_season);
        $this->assign('plan_code', $plan_code);

        return $this->fetch('detail');
    }
    
    /**
     *  财务结账明细报表
     * @return array|mixed
     */
    public function accountdetail()
    { 
        if(Request::isAjax())
        {
            $nd = input('year', 0);
            $jd = input('season', 0);
            $bm = input('dept', 0);
            
            // 这里直接调用SQL查询数据
            $model = new UModel();
            $list = $model->query("call proc_book_statistics_account_detail(".$nd.", ".$jd.", '".$bm."');");
            
            if(count($list) == 0){
                $re['code'] = 0;
                $re['msg'] = '';
                $re['count'] = 0;
                $re['data'] = [];
                return $re;
            }

            $dataArray = [];
            $summaryJeArray = [];
            $summaryNumArray = [];
            $lastKey = '';
            $totalJe = 0;
            $totalNum = 0;
            $bmmc = '';
            foreach($list[0] as $val){
                $key = $val['rxnd'].$val['xsxzmc'].$val['bmmc'];
                // 小计分组最后一条数据，插入小计
                if($lastKey != $key && $lastKey!= ''){
                    if(strpos($summaryJeArray[$lastKey], ',') == FALSE){
                        $dataArray[] = ['bmmc'=>$lastKey. ' 汇总', 'num'=>$summaryNumArray[$lastKey], 'je'=>number_format($summaryJeArray[$lastKey], 2)];
                    } else {
                        $dataArray[] = ['bmmc'=>$lastKey. ' 汇总', 'num'=>$summaryNumArray[$lastKey], 'je'=>$summaryJeArray[$lastKey]];
                    }
                } 

                $totalJe += $val['je'];
                $totalNum += $val['num'];
                if (array_key_exists($key, $summaryJeArray)) {
                    $summaryJeArray[$key] += $val['je'];
                    $summaryNumArray[$key] += $val['num'];
                } else {
                    $summaryJeArray[$key] = $val['je'];
                    $summaryNumArray[$key] = $val['num'];
                }
                $val['je'] = number_format($val['je'], 2);
                $dataArray[] = $val;
                $bmmc = $val['bmmc'];

                $lastKey = $key;
            }
            if(strpos($summaryJeArray[$lastKey], ',') == FALSE){
                $dataArray[] = ['bmmc'=>$lastKey. '汇总', 'num'=>$summaryNumArray[$lastKey], 'je'=>number_format($summaryJeArray[$lastKey], 2)];
            } else {
                $dataArray[] = ['bmmc'=>$lastKey. '汇总', 'num'=>$summaryNumArray[$lastKey], 'je'=>$summaryJeArray[$lastKey]];
            }

            if(strpos($totalJe, ',') == FALSE){
                $dataArray[] = ['bmmc'=>$bmmc. '总计', 'num'=>$totalNum, 'je'=>number_format($totalJe, 2)];
            } else {
                $dataArray[] = ['bmmc'=>$bmmc. '总计', 'num'=>$totalNum, 'je'=>$totalJe];
            }

            $re['code'] = 0;
            $re['msg'] = '';
            $re['count'] = count($dataArray);
            $re['data'] = $dataArray;
            return $re;
        }

        //获取部门列表
        $viewGetDeptsModel = new ViewGetDeptsModel();
        $dep_nameList = $viewGetDeptsModel->select();
        $this->assign('depList', $dep_nameList);

        //获取年度列表
        $viewGetYearsModel = new ViewGetYearsModel();
        $year_List = $viewGetYearsModel->select();
        $this->assign('yearList', $year_List);

        //获取季度列表
        $viewGetSeasonsModel = new ViewGetSeasonsModel();
        $season_List = $viewGetSeasonsModel->select();
        $this->assign('seasonList', $season_List);
        
        //获取当前年度季度
        $ConfigsModel = new ConfigsModel();
        $year = $ConfigsModel->getYear();
        $season = $ConfigsModel->getSeason();
        $this->assign('curr_year', $year);
        $this->assign('curr_season', $season);

        return $this->fetch('accountdetail');
    }

    /**
     *  财务结账汇总报表
     * @return array|mixed
     */
    public function accountsummary()
    {
        if(Request::isAjax())
        {
            $nd = input('year', 0);
            $jd = input('season', 0);
            
            // 这里直接调用SQL查询数据
            $model = new UModel();
            $list = $model->query("call proc_book_statistics_account_summary(".$nd.", ".$jd.");");
            
            if(count($list) == 0){
                $re['code'] = 0;
                $re['msg'] = '';
                $re['count'] = 0;
                $re['data'] = [];
                return $re;
            }

            $dataArray = [];
            $summaryJeArray = [];
            $lastKey = '';
            $totalJe = 0;
            foreach($list[0] as $val){
                $key = $val['bmmc'];
                // 小计分组最后一条数据，插入小计
                if($lastKey != $key && $lastKey!= ''){
                    if(strpos($summaryJeArray[$lastKey], ',') == FALSE){
                        $dataArray[] = ['bmmc'=>$lastKey. '汇总', 'je'=>number_format($summaryJeArray[$lastKey], 2)];
                    } else {
                        $dataArray[] = ['bmmc'=>$lastKey. '汇总', 'je'=>$summaryJeArray[$lastKey]];
                    }
                } 

                $totalJe += $val['je'];
                if (array_key_exists($key, $summaryJeArray)) {
                    $summaryJeArray[$key] += $val['je'];
                } else {
                    $summaryJeArray[$key] = $val['je'];
                }
                $val['je'] = number_format($val['je'], 2);
                $val['rxndxz'] = $val['rxnd'].$val['xsxzmc'];
                $dataArray[] = $val;

                $lastKey = $key;
            }
            if(strpos($summaryJeArray[$lastKey], ',') == FALSE){
                $dataArray[] = ['bmmc'=>$lastKey. '汇总', 'je'=>number_format($summaryJeArray[$lastKey], 2)];
            } else {
                $dataArray[] = ['bmmc'=>$lastKey. '汇总', 'je'=>$summaryJeArray[$lastKey]];
            }

            if(strpos($totalJe, ',') == FALSE){
                $dataArray[] = ['bmmc'=>'总计', 'je'=>number_format($totalJe, 2)];
            } else {
                $dataArray[] = ['bmmc'=>'总计', 'je'=>$totalJe];
            }

            $re['code'] = 0;
            $re['msg'] = '';
            $re['count'] = count($dataArray);
            $re['data'] = $dataArray;
            return $re;
        }
  
        //获取年度列表
        $viewGetYearsModel = new ViewGetYearsModel();
        $year_List = $viewGetYearsModel->select();
        $this->assign('yearList', $year_List);

        //获取季度列表
        $viewGetSeasonsModel = new ViewGetSeasonsModel();
        $season_List = $viewGetSeasonsModel->select();
        $this->assign('seasonList', $season_List);
        
        //获取当前年度季度
        $ConfigsModel = new ConfigsModel();
        $year = $ConfigsModel->getYear();
        $season = $ConfigsModel->getSeason();
        $this->assign('curr_year', $year);
        $this->assign('curr_season', $season);

        return $this->fetch('accountsummary');
    }

    /**
     *  学生退费明细报表
     * @return array|mixed
     */
    public function refunddetail()
    {
        if(Request::isAjax())
        {
            // 参数解析
            $nd = input('year', 0);
            $bm = input('dept', '');
            $zy = input('specialty', '');
            $xh = input('xh', '');
            $xm = input('xm', '');
            $limit = input('limit', 0); 
            $page = input('page', 0);
            $index = ($page - 1) * $limit;
            
            // 判断学号存不存在
            $StudentInfoModel = new StudentInfoModel();
            if ($xh != '')
            {
                $num = $StudentInfoModel->where(['xh'=> $xh])->count();
                if ($num == 0)
                {
                    $re['code'] = 0;
                    $re['error'] = -1;
                    $re['msg'] = '学号不存在';
                    $re['count'] = 0;
                    $re['data'] = [];
                    return $re;
                }
            }
            
            // 这里直接调用SQL查询数据
            $model = new UModel();
            
            $list = $model->query("call proc_refund_detail(".$nd.",'".$bm."','".$zy."','".$xh."','".$xm."',".$index.",".$limit.");");
            
            if(count($list) == 0){
                $re['code'] = 0;
                $re['msg'] = '';
                $re['count'] = 0;
                $re['data'] = [];
                return $re;
            }

            $re['code'] = 0;
            $re['msg'] = '';
            $re['count'] = count($list[1]);
            $re['data'] = $list[0];
            return $re;
        }

         //获取部门列表
         $viewGetDeptsModel = new ViewGetDeptsModel();
         $dep_nameList = $viewGetDeptsModel->select();
         $this->assign('depList', $dep_nameList);

         //获取专业列表
         $ViewGetSpecicaltiesModel = new ViewGetSpecicaltiesModel();
         $specicaltie_List = $ViewGetSpecicaltiesModel->select();
         $this->assign('specicaltieList', $specicaltie_List);
 
         //获取年度列表
         $viewGetYearsModel = new ViewGetYearsModel();
         $year_List = $viewGetYearsModel->select();
         $this->assign('yearList', $year_List);
 
         //获取季度列表
       //  $viewGetSeasonsModel = new ViewGetSeasonsModel();
       //  $season_List = $viewGetSeasonsModel->select();
      //   $this->assign('seasonList', $season_List);
         
         //获取方向列表
      //   $ViewGetDirsModel = new ViewGetDirsModel();
      //   $dir_List = $ViewGetDirsModel->select();
     //    $this->assign('dirList', $dir_List);

         //获取当前年度
         $ConfigsModel = new ConfigsModel();
         $year = $ConfigsModel->getYear();
         $season = $ConfigsModel->getSeason();
         $this->assign('curr_year', $year);

        return $this->fetch('refunddetail');
    }

    /**
     *  学生退费汇总报表
     * @return array|mixed
     */
    public function refundsummary()
    {
        if(Request::isAjax())
        {
            // 参数解析
            $nd = input('year', 0);
            // $limit = input('limit', 0); 
            // $page = input('page', 0);
            // $index = ($page - 1) * $limit;
                  
            // 这里直接调用SQL查询数据
            $model = new UModel();
            $index = 0; 
            $limit = 100000;

            $list = $model->query("call proc_refund_summary(".$nd.",".$index.",".$limit.");");
            
            if(count($list) == 0){
                $re['code'] = 0;
                $re['msg'] = '';
                $re['count'] = 0;
                $re['data'] = [];
                return $re;
            }

            $re['code'] = 0;
            $re['msg'] = '';
            $re['count'] = count($list[1]);
            $re['data'] = $list[0];
            return $re;
        }

        //获取年度列表
        $viewGetYearsModel = new ViewGetYearsModel();
        $year_List = $viewGetYearsModel->select();
        $this->assign('yearList', $year_List);
        
        //获取当前年度季度
        $ConfigsModel = new ConfigsModel();
        $year = $ConfigsModel->getYear();
        $this->assign('curr_year', $year);

        return $this->fetch('refundsummary');
    }
 
    /**
     *  学院教材清单表
     * @return array|mixed
     */
    public function collegebooklist()
    { 
        if(Request::isAjax())
        {
            $nd = input('year', 0);
            $jd = input('season', 0);
            $bm = input('dept', 0);
            $limit = input('limit', 0); 
            $page = input('page', 0);
            $index = ($page - 1) * $limit;
    
            // 这里直接调用SQL查询数据
            $model = new UModel();
            $list = $model->query("call proc_book_statistics_college_booklist(".$nd.", ".$jd.", '".$bm."',".$index.",".$limit.");");
            
            if(count($list) == 0){
                $re['code'] = 0;
                $re['msg'] = '';
                $re['count'] = 0;
                $re['data'] = [];
                return $re;
            }
 
            $re['code'] = 0;
            $re['msg'] = '';
            $re['count'] = count($list[1]);
            $re['data'] = $list[0];
            return $re;
        }

        //获取部门列表
        $viewGetDeptsModel = new ViewGetDeptsModel();
        $dep_nameList = $viewGetDeptsModel->select();
        $this->assign('depList', $dep_nameList);

        //获取年度列表
        $viewGetYearsModel = new ViewGetYearsModel();
        $year_List = $viewGetYearsModel->select();
        $this->assign('yearList', $year_List);

        //获取季度列表
        $viewGetSeasonsModel = new ViewGetSeasonsModel();
        $season_List = $viewGetSeasonsModel->select();
        $this->assign('seasonList', $season_List);
        
        //获取当前年度季度
        $ConfigsModel = new ConfigsModel();
        $year = $ConfigsModel->getYear();
        $season = $ConfigsModel->getSeason();
        $this->assign('curr_year', $year);
        $this->assign('curr_season', $season);

        return $this->fetch('collegebooklist');
    }

    /**
     *  教材发放结算明细
     * @return array|mixed
     */
    public function settledetail()
    {
        if(Request::isAjax())
        {
            // 参数解析
            $nd = input('year', 0);
            $jd = input('season', 0);
            $bm = input('dept', '');
            $zy = input('specialty', '');
            $book_name = input('book_name', '');
        
            
            // 这里直接调用SQL查询数据
            $model = new UModel();
            $list = $model->query("call proc_book_settle_detail(".$nd.",".$jd.",'".$bm."','".$zy."','".$book_name."');");
    
            if(count($list) == 0){
                $re['code'] = 0;
                $re['msg'] = '';
                $re['count'] = 0;
                $re['data'] = [];
                return $re;
            }
            
            $dataArray = [];
            $summaryJeArray = [];
            $summaryNumArray = [];
            $lastKey = '';
            $totalJe = 0;
            $totalNum = 0;
            $bmmc = '';
            foreach($list[0] as $val){
                $key = $val['rxnd'].$val['zymc'].$val['bmmc'];
                // 小计分组最后一条数据，插入小计
                if($lastKey != $key && $lastKey!= ''){
                    if(strpos($summaryJeArray[$lastKey], ',') == FALSE){
                        $dataArray[] = ['bmmc'=>$lastKey. ' 汇总', 'settle_price'=>number_format($summaryNumArray[$lastKey], 2), 'total_price'=>number_format($summaryJeArray[$lastKey], 2)];
                    } else {
                        $dataArray[] = ['bmmc'=>$lastKey. ' 汇总', 'settle_price'=>$summaryNumArray[$lastKey], 'total_price'=>$summaryJeArray[$lastKey]];
                    }
                } 

                $totalJe += $val['total_price'];
                $totalNum += $val['settle_price'];
                if (array_key_exists($key, $summaryJeArray)) {
                    $summaryJeArray[$key] += $val['total_price'];
                    $summaryNumArray[$key] += $val['settle_price'];
                } else {
                    $summaryJeArray[$key] = $val['total_price'];
                    $summaryNumArray[$key] = $val['settle_price'];
                }
                $val['total_price'] = number_format($val['total_price'], 2);
                $val['settle_price'] = number_format($val['settle_price'], 2);
                $dataArray[] = $val;
                $bmmc = $val['bmmc'];

                $lastKey = $key;
            }

            if(strpos($summaryJeArray[$lastKey], ',') == FALSE){
                $dataArray[] = ['bmmc'=>$lastKey. ' 汇总', 'settle_price'=>number_format($summaryNumArray[$lastKey], 2), 'total_price'=>number_format($summaryJeArray[$lastKey], 2)];
            } else {
                $dataArray[] = ['bmmc'=>$lastKey. ' 汇总', 'settle_price'=>$summaryNumArray[$lastKey], 'total_price'=>$summaryJeArray[$lastKey]];
            }
    
        //    if(strpos($totalJe, ',') == FALSE){
       //         $dataArray[] = ['bmmc'=>$bmmc. '总计', 'settle_price'=>number_format($totalNum, 2), 'total_price'=>number_format($totalJe, 2)];
       //     } else {
        //        $dataArray[] = ['bmmc'=>$bmmc. '总计', 'settle_price'=>$totalNum, 'total_price'=>$totalJe];
       //     }

            $re['code'] = 0;
            $re['msg'] = '';
            $re['count'] = count($dataArray);
            $re['data'] = $dataArray;
            return $re;
        }

        

        //获取部门列表
        $viewGetDeptsModel = new ViewGetDeptsModel();
        $dep_nameList = $viewGetDeptsModel->select();
        $this->assign('depList', $dep_nameList);

        //获取年度列表
        $viewGetYearsModel = new ViewGetYearsModel();
        $year_List = $viewGetYearsModel->select();
        $this->assign('yearList', $year_List);

        //获取季度列表
        $viewGetSeasonsModel = new ViewGetSeasonsModel();
        $season_List = $viewGetSeasonsModel->select();
        $this->assign('seasonList', $season_List);

        //获取当前年度季度
        $ConfigsModel = new ConfigsModel();
        $year = $ConfigsModel->getYear();
        $season = $ConfigsModel->getSeason();
        $this->assign('curr_year', $year);
        $this->assign('curr_season', $season);


        return $this->fetch();
    }

    /**
     *  教材选用情况统计表
     * @return array|mixed
     */
    public function selecttotal()
    {
        if(Request::isAjax())
        {
            // 参数解析
            $nd = input('year', 0);
            $bm = input('dept', '');
            $zy = input('specialty', '');
            $book_name = input('book_name', '');
            $book_isbn = input('book_isbn', '');
            $book_reward = input('book_reward', '');
            $limit = input('limit', 0); 
            $page = input('page', 0);
            $index = ($page - 1) * $limit;
            
            // 这里直接调用SQL查询数据
            $model = new UModel();
            $list = $model->query("call proc_book_select_total(".$nd.",'".$bm."','".$zy."','".$book_isbn."','".$book_name."','".$book_reward."',".$index.",".$limit.");");
    
            if(count($list) == 0){
                $re['code'] = 0;
                $re['msg'] = '';
                $re['count'] = 0;
                $re['data'] = [];
                return $re;
            }

            $re['code'] = 0;
            $re['msg'] = '';
            $re['count'] = count($list[1]);
            $re['data'] = $list[0];
            return $re;
        }

        //获取部门列表
        $viewGetDeptsModel = new ViewGetDeptsModel();
        $dep_nameList = $viewGetDeptsModel->select();
        $this->assign('depList', $dep_nameList);

        //获取年度列表
        $viewGetYearsModel = new ViewGetYearsModel();
        $year_List = $viewGetYearsModel->select();
        $this->assign('yearList', $year_List);

        //获取获奖情况列表
        $ViewGetBookRewardModel = new ViewGetBookRewardModel();
        $reward_List = $ViewGetBookRewardModel->select();
        $this->assign('rewardList', $reward_List);
        
        
        //获取当前年度季度
        $ConfigsModel = new ConfigsModel();
        $year = $ConfigsModel->getYear();
        $this->assign('curr_year', $year);
       


        return $this->fetch();
    }
}